package service

import (
	"encoding/csv"
	"encoding/json"
	"fmt"
	"io"
	"io/ioutil"
	"math"
	"nt-order/common"
	"nt-order/conf"
	"nt-order/model"
	f "nt-order/route/form"
	"os"
	"reflect"
	"strconv"
	"strings"
	"sync"
	"time"
	"xorm.io/xorm"
)

var wg sync.WaitGroup
var mx sync.Mutex

type OrderDataExportLogic struct {
}

func (m *OrderDataExportLogic) OrderDataExport(form *f.IndexForm) int {
	db := conf.GetDb()
	fileName := form.StaffName + "-" + time.Now().Format("20060102T150405") + ".xls"
	now := time.Now()
	dataProcess := model.OrderDataProcess{
		FileName:   fileName,
		FileDir:    "D:/nantang/order-go/runtime/export/" + fileName,
		DataType:   0,
		DataStatus: 0,
		StaffId:    form.StaffId,
		CreateUser: form.StaffName,
		CreatedAt:  now.Format("2006-01-02 15:04:05"),
		UpdatedAt:  now.Format("2006-01-02 15:04:05"),
	}
	processId := new(model.OrderDataProcess).Insert(dataProcess)

	defer func() {
		if err := recover(); err != nil {
			now := time.Now()
			dataProcess := model.OrderDataProcess{
				DataStatus: 2,
				UpdatedAt:  now.Format("2006-01-02 15:04:05"),
			}
			_, _ = db.ID(processId).Update(dataProcess)
			common.WriteLog(err)
		}
	}()

	where := " where 1=1 "
	bind := make([]interface{}, 0)

	orderCodes := make([]string, 0)
	if !strings.EqualFold(form.Order_code, "") {
		orderCodes = append(orderCodes, common.SplitMultipleItem(form.Order_code)...)
	}
	if len(form.Order_code_arr) > 0 {
		orderCodes = append(orderCodes, form.Order_code_arr...)
	}

	//备注信息
	if form.Order_comment != "" {
		tmp := make([]struct{ OrderCode string }, 0)
		err := db.Table("order_comment").Where("oc_content like '" + form.Order_comment + "%'").Find(&tmp)
		if err != nil {
			panic(err)
		}
		for _, product := range tmp {
			orderCodes = append(orderCodes, product.OrderCode)
		}
	}

	orderCodes = common.RemoveRepeatedElementAndEmpty(orderCodes)
	if len(orderCodes) > 0 {
		where += " and o.order_code in(" + common.FillQuestionnaire(len(orderCodes)) + ") "
		for _, code := range orderCodes {
			bind = append(bind, code)
		}
	}

	//店铺订单号
	if !strings.EqualFold(form.Store_order_code, "") {
		codes := common.SplitMultipleItem(form.Store_order_code)
		where += " and o.store_order_code in(" + common.FillQuestionnaire(len(codes)) + ") "
		for _, s := range codes {
			bind = append(bind, s)
		}
	}

	//平台
	if len(form.Platform) > 0 {
		platforms := common.RemoveRepeatedElementAndEmpty(form.Platform)
		if len(platforms) > 0 {
			where += " and o.platform in(" + common.FillQuestionnaire(len(platforms)) + ") "
			for _, val := range platforms {
				bind = append(bind, val)
			}
		}
	}

	//手机号
	if !strings.EqualFold(form.Ship_phone, "") {
		where += " and o.ship_phone=? "
		bind = append(bind, form.Ship_phone)
	}

	//时间
	if form.Time_type != "" && form.Datetime_start != "" && form.Datetime_end != "" {
		tt := common.IntVal(form.Time_type)
		switch tt {
		case 1:
			where += " and o.store_created_at>=? and o.store_created_at<=? "
			bind = append(bind, form.Datetime_start, form.Datetime_end)
		case 2:
			where += " and o.date_payment>=? and o.date_payment<=? "
			bind = append(bind, form.Datetime_start, form.Datetime_end)
		case 3:
			where += " and o.created_at>=? and o.created_at<=? "
			bind = append(bind, form.Datetime_start, form.Datetime_end)
		case 4:
			where += " and o.ship_date>=? and o.ship_date<=? "
			bind = append(bind, form.Datetime_start, form.Datetime_end)
		}
	}

	//标记异常
	if form.Fulfill_exception != "" {
		where += " and o.fulfill_exception=? "
		bind = append(bind, form.Fulfill_exception)
	}

	//收件人姓名
	form.Ship_name = strings.TrimSpace(form.Ship_name)
	if form.Ship_name != "" {
		where += " and (o.ship_first_name like '" + form.Ship_name + "%' or o.ship_last_name like '" + form.Ship_name + "%') "
	}

	//收货国家
	if form.Ship_country != "" {
		where += " and o.ship_country=? "
		bind = append(bind, form.Ship_country)
	}

	//仓库信息
	if form.Warehouse_id != "" && form.Warehouse_id != "0" {
		where += " and o.warehouse_id=? "
		bind = append(bind, form.Warehouse_id)
	}

	//订单金额
	if form.Grand_total_min != "" && form.Grand_total_min != "0" {
		where += " and o.grand_total>=? "
		bind = append(bind, form.Grand_total_min)
	}
	if form.Grand_total_max != "" && form.Grand_total_max != "0" {
		where += " and o.grand_total<=? "
		bind = append(bind, form.Grand_total_max)
	}

	//item id *店铺的listing ID (不是店铺产品id)
	if form.Item_id != "" {
		itemIds := common.SplitMultipleItem(form.Item_id)
		if len(itemIds) > 0 {
			where += " and p.store_item_id in(" + common.FillQuestionnaire(len(itemIds)) + ") "
			for _, v := range itemIds {
				bind = append(bind, v)
			}
		}
	}

	//公司sku信息
	if form.Product_id != "" {
		productIds := common.SplitMultipleItem(form.Product_id)
		if len(productIds) > 0 {
			skuType := common.IntVal(form.Sku_type)
			if skuType == 1 {
				where += " and p.product_id in(" + common.FillQuestionnaire(len(productIds)) + ") "
				for _, v := range productIds {
					bind = append(bind, v)
				}
			} else if skuType == 2 {
				where += " and p.store_item_number in(" + common.FillQuestionnaire(len(productIds)) + ") "
				for _, v := range productIds {
					bind = append(bind, v)
				}
			} else if skuType == 3 {
				where += " and p.store_item_id in(" + common.FillQuestionnaire(len(productIds)) + ") "
				for _, v := range productIds {
					bind = append(bind, v)
				}
			}
		}
	}

	//是否标记发货
	if form.IsFulfill != "" && form.IsFulfill != "0" {
		isFulfill := common.IntVal(form.IsFulfill)
		if isFulfill == 2 {
			where += " and o.track_number!='' "
		} else if isFulfill == 1 {
			where += " and o.track_number='' "
		}
	}

	//销售记录编号搜索
	if form.Sales_record_number != "" {
		tmp := common.SplitMultipleItem(form.Sales_record_number)
		if len(tmp) > 0 {
			where += " and o.sales_record_number in(" + common.FillQuestionnaire(len(tmp)) + ") "
			for _, s := range tmp {
				bind = append(bind, s)
			}
		}
	}

	//是否代发
	form.Is_fba_behalf = strings.TrimSpace(form.Is_fba_behalf)
	if form.Is_fba_behalf != "" {
		if form.Is_fba_behalf != "0" {
			where += " and o.is_fba_behalf=1 "
		} else {
			where += " and o.is_fba_behalf=0 "
		}
	}

	//运输方式
	if form.Shipping_method_orig != "" {
		where += " and o.shipping_method_orig like '%" + form.Shipping_method_orig + "%' "
	}

	//物流方式
	if form.Shipping_method != "" {
		tmp := common.SplitMultipleItem(form.Shipping_method)
		if len(tmp) > 0 {
			where += " and o.shipping_method in(" + common.FillQuestionnaire(len(tmp)) + ") "
			for _, s := range tmp {
				bind = append(bind, s)
			}
		}
	}

	//海外仓状态
	if form.Overseas_warehouse_status != "" {
		tmp := common.SplitMultipleItem(form.Overseas_warehouse_status)
		if len(tmp) > 0 {
			where += " and o.overseas_warehouse_status in(" + common.FillQuestionnaire(len(tmp)) + ") "
			for _, s := range tmp {
				bind = append(bind, s)
			}
		}
	}

	//跟踪号查询
	if form.FollowNumber != "" {
		tmp := common.SplitMultipleItem(form.FollowNumber)
		if len(tmp) > 0 {
			where += " and (o.follow_number in(" + common.FillQuestionnaire(len(tmp)) + ") or o.track_number in(" + common.FillQuestionnaire(len(tmp)) + ")) "
			for _, s := range tmp {
				bind = append(bind, s, s)
			}
		}
	}

	// 平台特有: 交易号查询
	if form.Transaction_number != "" {
		tmp := common.SplitMultipleItem(form.Transaction_number)
		if len(tmp) > 0 {
			where += " and o.transaction_number in(" + common.FillQuestionnaire(len(tmp)) + ") "
			for _, s := range tmp {
				bind = append(bind, s)
			}
		}
	}

	//店铺
	if len(form.Shop_name) > 0 {
		where += " and o.shop_name in(" + common.FillQuestionnaire(len(form.Shop_name)) + ") "
		for _, s := range form.Shop_name {
			if "" != strings.TrimSpace(s) {
				bind = append(bind, s)
			}
		}
	}

	//订单状态
	if len(form.Order_status) > 0 {
		where += " and o.order_status in(" + common.FillQuestionnaire(len(form.Order_status)) + ") "
		for _, s := range form.Order_status {
			if "" != strings.TrimSpace(s) {
				bind = append(bind, s)
			}
		}
	}

	if form.ExceptionType != "" || form.Is_exception != "" {
		if form.Is_exception != "" {
			isException := common.IntVal(form.Is_exception)
			if isException > 0 {
				where += " and o.exception_type!=0 "
			} else {
				where += " and o.exception_type=0 "
			}
		}
		if form.ExceptionType != "" {
			where += " and o.exception_type=? "
			bind = append(bind, form.ExceptionType)
		}
	}

	if form.Order_list_type != "" {
		tmp := common.SplitMultipleItem(form.Order_list_type)
		if len(tmp) > 0 {
			where += " and o.order_list_type in(" + common.FillQuestionnaire(len(tmp)) + ") "
			for _, s := range tmp {
				bind = append(bind, s)
			}
		}
	}

	if form.Fulfill_type != "" {
		where += " and o.fulfill_type=? "
		bind = append(bind, form.Fulfill_type)
	}

	if form.OrderType != "" {
		if form.OrderType == "4" {
			where += " and o.order_type=? "
			bind = append(bind, 2)
		} else if form.OrderType == "5" {
			where += " and o.order_cretate_type=? "
			bind = append(bind, "create")
		} else {
			where += " and o.order_type=? "
			bind = append(bind, form.OrderType)
		}
	}

	if form.Create_Order_Status != "" {
		where += " and o.create_order_status=? "
		bind = append(bind, form.Create_Order_Status)
	}

	auth := new(model.OrderAuth)
	w, b := auth.Where(form.StaffId)
	if w != "" && b != nil {
		where += w
		bind = append(bind, b...)
	}

	realNeedFds := make([]*FieldKv, 0)
	needFields := getNeetFields(form)
	authInfo := auth.First(form.StaffId)
	if authInfo != nil {
		authFds := make([]string, 0)
		for _, filed := range authInfo.Field {
			for _, item := range filed.List {
				authFds = append(authFds, item.Key)
			}
		}
		authFds = common.RemoveRepeatedElementAndEmpty(authFds)
		for _, field := range needFields {
			exist := false
			for _, fd := range authFds {
				if fd == field.Field {
					exist = true
				}
			}
			if exist {
				realNeedFds = append(realNeedFds, field)
			}
		}
	} else {
		realNeedFds = needFields
	}

	filePath := "/home/nt/php/nt-order/php/storage/app/public/export/"
	totalRs := make(map[string]string)
	sqlQ := fmt.Sprintf("select count(0) total from order_main o left join order_product p on o.order_code=p.order_code %s", where)
	//log.Fatalln(sqlQ)
	_, err := db.SQL(sqlQ, bind...).Get(&totalRs)
	if err != nil {
		panic(err)
	}
	total := common.IntVal(totalRs["total"])
	if total == 0 {
		excel, err := os.OpenFile(filePath+fileName, os.O_RDWR|os.O_CREATE, os.ModePerm)
		if err != nil {
			panic(err)
		}
		defer excel.Close()
		_, _ = excel.WriteString("\xEF\xBB\xBF") // 写入UTF-8 BOM
		w := csv.NewWriter(excel)
		_ = w.Write([]string{"没有可以导出的数据"})
		w.Flush()

		dataProcess = model.OrderDataProcess{
			DataStatus: 1,
			UpdatedAt:  now.Format("2006-01-02 15:04:05"),
		}
		_, err = db.ID(processId).Update(dataProcess)
		if err != nil {
			panic(err)
		}
		return 0
	}
	//log.Println(total)

	pageSize := 1000
	pageCount := total / pageSize
	if total%pageSize != 0 {
		pageCount = pageCount + 1
	}
	if total <= pageSize {
		pageSize = total
	}

	if pageCount > 0 {
		excel, err := os.OpenFile(filePath+fileName, os.O_RDWR|os.O_CREATE, os.ModePerm)
		//excel, err := os.OpenFile("runtime/export/"+fileName, os.O_RDWR|os.O_CREATE, os.ModePerm)
		if err != nil {
			panic(err)
		}
		defer excel.Close()
		_, _ = excel.WriteString("\xEF\xBB\xBF") // 写入UTF-8 BOM

		titleCn := make([]string, 0)
		for _, field := range realNeedFds {
			titleCn = append(titleCn, field.FieldName)
		}
		w := csv.NewWriter(excel)
		_ = w.Write(titleCn)
		w.Flush()

		ppLen, ppSize := 8, 8
		pp := pageCount / ppSize
		ppLast := 0
		if pageCount%ppSize != 0 {
			ppLast = pageCount % ppSize
			pp = pp + 1
		}

		mx.Lock()
		if pageCount <= ppSize {
			wg.Add(pageCount)
			for i := 1; i <= pageCount; i++ {
				if form.Export_style == "2" {
					go exportExcelOrder(i, pageSize, db, excel, realNeedFds, where, bind, form)
				} else {
					go exportExcel(i, pageSize, db, excel, realNeedFds, where, bind, form)
				}
			}
			wg.Wait()
		} else {
			for i := 1; i <= pp; i++ {
				if i == pp && ppLast > 0 {
					ppLen = ppLast
				}

				wg.Add(ppLen)
				for j := 1; j <= ppLen; j++ {
					if form.Export_style == "2" {
						go exportExcelOrder(j+(i-1)*ppSize, pageSize, db, excel, realNeedFds, where, bind, form)
					} else {
						go exportExcel(j+(i-1)*ppSize, pageSize, db, excel, realNeedFds, where, bind, form)
					}
				}
				wg.Wait()

			}
		}
		mx.Unlock()
	}

	dataProcess = model.OrderDataProcess{
		DataStatus: 1,
		UpdatedAt:  now.Format("2006-01-02 15:04:05"),
	}
	_, err = db.ID(processId).Update(dataProcess)
	if err != nil {
		panic(err)
	}

	return total
}

func exportExcel(pageIndex, pageSize int, db *xorm.Engine, excel *os.File, realNeedFds []*FieldKv, where string, bind []interface{}, form *f.IndexForm) {
	orderList := make([]ExcelOrder, 0)
	sqlQ := fmt.Sprintf("select * from order_main o left join order_product p on o.order_code=p.order_code %s limit %d,%d", where, (pageIndex-1)*pageSize, pageSize)
	err := db.SQL(sqlQ, bind...).Find(&orderList)
	if err != nil {
		panic(err)
	}

	skus := make([]string, 0)
	shipMethods := make([]string, 0)
	codes := make([]string, 0)
	for _, order := range orderList {
		skus = append(skus, order.Product_id)
		shipMethods = append(shipMethods, order.Shipping_method)
		codes = append(codes, order.OrderMain.Order_code)
	}
	skus = common.RemoveRepeatedElementAndEmpty(skus)

	productDb := conf.GetDb("product")
	skuInfos := make([]SkuInfo, 0)
	err = productDb.Table("nt_product_pool").Alias("p").Join("INNER", []string{"nt_sku", "s"}, "p.sku=s.sku").In("p.sku", skus).Find(&skuInfos)
	if err != nil {
		panic(err)
	}

	shipMethodList := make([]ShipMethod, 0)
	err = db.Table("shipping_method").In("mode_code", shipMethods).Find(&shipMethodList)
	if err != nil {
		panic(err)
	}

	orderReverseList := make([]OrderReverse, 0)
	err = db.Table("order_reverse").In("rel_order_code", codes).Find(&orderReverseList)
	if err != nil {
		panic(err)
	}

	orderOtherList := make([]OrderOther, 0)
	err = db.Table("order_other").In("order_code", codes).Find(&orderOtherList)
	if err != nil {
		panic(err)
	}

	wList := new(model.Warehouse).GetWarehouse()
	orderStatus := map[int]string{
		1:  "待物流匹配",
		2:  "待派单",
		21: "待取跟踪号",
		23: "待获取物流面单",
		3:  "待拣货",
		4:  "待核单",
		6:  "待发货",
		22: "fba代发",
		7:  "已发货",
	}

	oWarehouseStatusKV := new(CommonLogic).GetOverseasWarehouseStatus(true)

	excel.Seek(0, io.SeekEnd)
	w := csv.NewWriter(excel)

	orderStaffModel := new(model.OrderStaffList)
	for _, order := range orderList {
		for _, skuInfo := range skuInfos {
			if skuInfo.Sku == order.Product_id {
				order.Buy_price = skuInfo.Buy_price
				order.Pack_weight = skuInfo.Pack_weight * order.Product_quantity
				order.Order_net_weight = skuInfo.Weight * order.Product_quantity
				order.Sku_name = skuInfo.Sku_name
				order.Attr_trans = skuInfo.Attr_trans
				order.Sale_state = skuInfo.Sale_state
				order.Developer_name = skuInfo.Developer_name
				order.Designer_name = skuInfo.Designer_name
				order.Purchaser_name = skuInfo.Purchaser
				if skuInfo.Sale_state == 1 {
					order.Sale_state_str = "在售"
				} else {
					order.Sale_state_str = "停售"
				}
				break
			}
		}
		order.Purchase_cost = float64(order.Product_quantity) * order.Buy_price
		status, ok := orderStatus[order.Order_status]
		if ok {
			order.Order_status_str = status
		} else {
			order.Order_status_str = "未知"
		}
		if order.Shipping_method != "" {
			if len(shipMethodList) > 0 {
				for _, shipMethod := range shipMethodList {
					if shipMethod.Mode_code == order.Shipping_method {
						order.Shipping_company = shipMethod.Logistics_company_name
						order.Shipping_method = shipMethod.Mode_name
						order.Mode_name = shipMethod.Mode_name
						break
					}
				}
			}
		}
		if len(orderReverseList) > 0 {
			flag := true
			for k, reverse := range orderReverseList {
				if reverse.Rel_order_code == order.OrderMain.Order_code {
					if k > 0 {
						order.After_sale_number += ","
					}
					order.After_sale_number += reverse.Or_code

					if flag {
						if reverse.Reverse_type == 1 {
							order.Reverse_type = "退款"
						} else if reverse.Reverse_type == 2 {
							order.Reverse_type = "重寄"
						}

						if reverse.Reverse_status == 1 {
							order.Reverse_status = "已完成"
						} else if reverse.Reverse_status == 2 {
							order.Reverse_status = "未完成"
						} else if reverse.Reverse_status == 3 {
							order.Reverse_status = "作废"
						}

						order.Or_amount = reverse.Or_amount
						flag = false
					}
				}
			}
		}
		if order.Order_list_type == 2 {
			order.Order_type_str = "作废订单"
		} else {
			order.Order_type_str = order.GetOrderTypeAttribute(order.Order_type)
		}
		if order.Is_fba_behalf == 1 {
			order.Is_fba_behalf_str = "是"
		} else {
			order.Is_fba_behalf_str = "否"
		}

		order.Ship_last_name = order.Ship_first_name + " " + order.Ship_last_name
		order.Store_order_weight = order.Order_weight

		outGrandTotal := getOrderProfitDenominator(order)
		order.Out_grand_total = outGrandTotal
		if order.Order_profit == 0 || outGrandTotal == 0 {
			order.Order_profit_rate = 0
		} else {
			order.Order_profit_rate = common.Float64Val(fmt.Sprintf("%.4f", order.Order_profit/outGrandTotal*100), 64)
		}
		order.Order_profit_rate_str = fmt.Sprintf("%v%%", order.Order_profit_rate)

		if len(orderOtherList) > 0 {
			pick_user := orderStaffModel.GetStaffName(orderOtherList[0].Pick_user)
			if pick_user != "" {
				order.Pick_user = pick_user
			} else {
				order.Pick_user = "system"
			}
			pack_user := orderStaffModel.GetStaffName(orderOtherList[0].Pack_user)
			if pack_user != "" {
				order.Pack_user = pack_user
			} else {
				order.Pack_user = "system"
			}
			ex_warehouse_time := orderStaffModel.GetStaffName(orderOtherList[0].Ex_warehouse_time)
			if ex_warehouse_time != "" {
				order.Ex_warehouse_time = ex_warehouse_time
			} else {
				order.Ex_warehouse_time = ""
			}
		}

		if len(wList) > 0 {
			wid := common.IntVal(order.Warehouse_id)
			for _, warehouse := range wList {
				if int(warehouse.Id) == wid {
					order.Warehouse_name = warehouse.Name
					break
				}
			}
		}

		if order.Platform == "ebay" {
			orderPayment := OrderPayment{}
			get, err := db.Table("order_payment").Where("order_code=?", order.OrderMain.Order_code).Limit(1).Get(&orderPayment)
			if err != nil {
				panic(err)
			}
			if get {
				order.Op_paypal_account = orderPayment.Op_paypal_account
				order.Transaction_number = orderPayment.Transaction_number
			}
		}

		orderCreateAt, err := time.ParseInLocation("2006-01-02 15:04:05", "2022-01-21 16:34:28", time.Local)
		if err != nil {
			order.Delay_days = 0
		} else {
			order.Delay_days = int64(math.Ceil(time.Now().Sub(orderCreateAt).Seconds() / 86400))
		}

		if ows, ok := oWarehouseStatusKV[order.Overseas_warehouse_status]; ok {
			order.Overseas_warehouse_status_str = ows
		}

		countryInfo := new(ShopWebLogic).GetCountryNameInfo(order.Ship_country_code)
		if countryInfo != nil {
			order.Ship_country_code = countryInfo.Zh_name
		}
		order.Store_order_code = "`" + order.Store_order_code

		order.Product_quantity_str = fmt.Sprintf("%d", order.Product_quantity)
		order.Order_net_weight_str = fmt.Sprintf("%d", order.Order_net_weight)
		order.Pack_weight_str = fmt.Sprintf("%d", order.Pack_weight)
		order.Purchase_cost_str = fmt.Sprintf("%.4f", order.Purchase_cost)
		order.Store_order_weight_str = fmt.Sprintf("%.4f", order.Store_order_weight)
		order.Weight_str = fmt.Sprintf("%d", order.Weight)

		orderType := reflect.TypeOf(order)
		orderValue := reflect.ValueOf(order)
		valCn := make([]string, 0)
		for _, fd := range realNeedFds {
			fkey := common.UFirst(fd.Field)
			if _, okey := orderType.FieldByName(fkey + "_str"); okey {
				fkey = fkey + "_str"
			}
			if _, ok := orderType.FieldByName(fkey); ok {
				val := orderValue.FieldByName(fkey)
				switch val.Kind() {
				case reflect.String:
					valCn = append(valCn, val.String())
				case reflect.Float64:
					valCn = append(valCn, strconv.FormatFloat(val.Float(), 'G', -1, 64))
				case reflect.Int:
					valCn = append(valCn, strconv.FormatInt(val.Int(), 10))
				case reflect.Int64:
					valCn = append(valCn, strconv.FormatInt(val.Int(), 10))
				case reflect.Bool:
					valCn = append(valCn, strconv.FormatBool(val.Bool()))
				}
			} else {
				valCn = append(valCn, "")
			}
		}
		_ = w.Write(valCn)
		w.Flush()
	}

	//log.Println(fmt.Sprintf("%d,%d", pageIndex, pageSize))

	wg.Done()

}

func exportExcelOrder(pageIndex, pageSize int, db *xorm.Engine, excel *os.File, realNeedFds []*FieldKv, where string, bind []interface{}, form *f.IndexForm) {
	orderList := make([]ExcelOrder, 0)
	sqlQ := fmt.Sprintf("select * from order_main o left join order_product p on o.order_code=p.order_code %s limit %d,%d", where, (pageIndex-1)*pageSize, pageSize)
	err := db.SQL(sqlQ, bind...).Find(&orderList)
	if err != nil {
		panic(err)
	}

	skus := make([]string, 0)
	shipMethods := make([]string, 0)
	codes := make([]string, 0)
	for _, order := range orderList {
		skus = append(skus, order.Product_id)
		shipMethods = append(shipMethods, order.Shipping_method)
		codes = append(codes, order.OrderMain.Order_code)
	}
	skus = common.RemoveRepeatedElementAndEmpty(skus)

	productDb := conf.GetDb("product")
	skuInfos := make([]SkuInfo, 0)
	err = productDb.Table("nt_product_pool").Alias("p").Join("INNER", []string{"nt_sku", "s"}, "p.sku=s.sku").In("p.sku", skus).Find(&skuInfos)
	if err != nil {
		panic(err)
	}

	shipMethodList := make([]ShipMethod, 0)
	err = db.Table("shipping_method").In("mode_code", shipMethods).Find(&shipMethodList)
	if err != nil {
		panic(err)
	}

	orderReverseList := make([]OrderReverse, 0)
	err = db.Table("order_reverse").In("rel_order_code", codes).Find(&orderReverseList)
	if err != nil {
		panic(err)
	}

	orderOtherList := make([]OrderOther, 0)
	err = db.Table("order_other").In("order_code", codes).Find(&orderOtherList)
	if err != nil {
		panic(err)
	}

	wList := new(model.Warehouse).GetWarehouse()
	orderStatus := map[int]string{
		1:  "待物流匹配",
		2:  "待派单",
		21: "待取跟踪号",
		23: "待获取物流面单",
		3:  "待拣货",
		4:  "待核单",
		6:  "待发货",
		22: "fba代发",
		7:  "已发货",
	}

	oWarehouseStatusKV := new(CommonLogic).GetOverseasWarehouseStatus(true)

	excel.Seek(0, io.SeekEnd)
	w := csv.NewWriter(excel)

	orderStaffModel := new(model.OrderStaffList)
	for okey, order := range orderList {
		for _, skuInfo := range skuInfos {
			if skuInfo.Sku == order.Product_id {
				order.Buy_price = skuInfo.Buy_price
				order.Pack_weight = skuInfo.Pack_weight * order.Product_quantity
				order.Order_net_weight = skuInfo.Weight * order.Product_quantity
				order.Sku_name = skuInfo.Sku_name
				order.Attr_trans = skuInfo.Attr_trans
				order.Sale_state = skuInfo.Sale_state
				order.Developer_name = skuInfo.Developer_name
				order.Designer_name = skuInfo.Designer_name
				order.Purchaser_name = skuInfo.Purchaser
				if skuInfo.Sale_state == 1 {
					order.Sale_state_str = "在售"
				} else {
					order.Sale_state_str = "停售"
				}
				break
			}
		}
		order.Purchase_cost = float64(order.Product_quantity) * order.Buy_price
		status, ok := orderStatus[order.Order_status]
		if ok {
			order.Order_status_str = status
		} else {
			order.Order_status_str = "未知"
		}
		if order.Shipping_method != "" {
			if len(shipMethodList) > 0 {
				for _, shipMethod := range shipMethodList {
					if shipMethod.Mode_code == order.Shipping_method {
						order.Shipping_company = shipMethod.Logistics_company_name
						order.Shipping_method = shipMethod.Mode_name
						order.Mode_name = shipMethod.Mode_name
						break
					}
				}
			}
		}
		if len(orderReverseList) > 0 {
			flag := true
			for k, reverse := range orderReverseList {
				if reverse.Rel_order_code == order.OrderMain.Order_code {
					if k > 0 {
						order.After_sale_number += ","
					}
					order.After_sale_number += reverse.Or_code

					if flag {
						if reverse.Reverse_type == 1 {
							order.Reverse_type = "退款"
						} else if reverse.Reverse_type == 2 {
							order.Reverse_type = "重寄"
						}

						if reverse.Reverse_status == 1 {
							order.Reverse_status = "已完成"
						} else if reverse.Reverse_status == 2 {
							order.Reverse_status = "未完成"
						} else if reverse.Reverse_status == 3 {
							order.Reverse_status = "作废"
						}

						order.Or_amount = reverse.Or_amount
						flag = false
					}
				}
			}
		}
		if order.Order_list_type == 2 {
			order.Order_type_str = "作废订单"
		} else {
			order.Order_type_str = order.GetOrderTypeAttribute(order.Order_type)
		}
		if order.Is_fba_behalf == 1 {
			order.Is_fba_behalf_str = "是"
		} else {
			order.Is_fba_behalf_str = "否"
		}

		order.Ship_last_name = order.Ship_first_name + " " + order.Ship_last_name
		order.Store_order_weight = order.Order_weight

		outGrandTotal := getOrderProfitDenominator(order)
		order.Out_grand_total = outGrandTotal
		if order.Order_profit == 0 || outGrandTotal == 0 {
			order.Order_profit_rate = 0
		} else {
			order.Order_profit_rate = common.Float64Val(fmt.Sprintf("%.4f", order.Order_profit/outGrandTotal*100), 64)
		}
		order.Order_profit_rate_str = fmt.Sprintf("%v%%", order.Order_profit_rate)

		if len(orderOtherList) > 0 {
			pick_user := orderStaffModel.GetStaffName(orderOtherList[0].Pick_user)
			if pick_user != "" {
				order.Pick_user = pick_user
			} else {
				order.Pick_user = "system"
			}
			pack_user := orderStaffModel.GetStaffName(orderOtherList[0].Pack_user)
			if pack_user != "" {
				order.Pack_user = pack_user
			} else {
				order.Pack_user = "system"
			}
			ex_warehouse_time := orderStaffModel.GetStaffName(orderOtherList[0].Ex_warehouse_time)
			if ex_warehouse_time != "" {
				order.Ex_warehouse_time = ex_warehouse_time
			} else {
				order.Ex_warehouse_time = ""
			}
		}

		if len(wList) > 0 {
			wid := common.IntVal(order.Warehouse_id)
			for _, warehouse := range wList {
				if int(warehouse.Id) == wid {
					order.Warehouse_name = warehouse.Name
					break
				}
			}
		}

		if order.Platform == "ebay" {
			orderPayment := OrderPayment{}
			get, err := db.Table("order_payment").Where("order_code=?", order.OrderMain.Order_code).Limit(1).Get(&orderPayment)
			if err != nil {
				panic(err)
			}
			if get {
				order.Op_paypal_account = orderPayment.Op_paypal_account
				order.Transaction_number = orderPayment.Transaction_number
			}
		}

		orderCreateAt, err := time.ParseInLocation("2006-01-02 15:04:05", order.Store_created_at, time.Local)
		if err != nil {
			order.Delay_days = 0
		} else {
			order.Delay_days = int64(math.Ceil(time.Now().Sub(orderCreateAt).Seconds() / 86400))
		}

		if ows, ok := oWarehouseStatusKV[order.Overseas_warehouse_status]; ok {
			order.Overseas_warehouse_status_str = ows
		}

		countryInfo := new(ShopWebLogic).GetCountryNameInfo(order.Ship_country_code)
		if countryInfo != nil {
			order.Ship_country_code = countryInfo.Zh_name
		}
		order.Store_order_code = "`" + order.Store_order_code
		order.Follow_number = "`" + order.Follow_number
		order.Product_quantity_str = strconv.Itoa(order.Product_quantity)
		order.Order_net_weight_str = strconv.Itoa(order.Order_net_weight)
		order.Pack_weight_str = strconv.Itoa(order.Pack_weight)
		order.Weight_str = fmt.Sprintf("%d", order.Weight)
		order.Purchase_cost_str = strconv.FormatFloat(order.Purchase_cost, 'f', 2, 64)
		order.Store_order_weight_str = strconv.FormatFloat(order.Store_order_weight, 'f', 2, 64)
		orderList[okey] = order
	}

	oList := make(map[string]ExcelOrder, 0)
	for _, oo := range orderList {
		bb, ok := oList[oo.Order_code]
		if !ok {
			oList[oo.Order_code] = oo
			bb = oList[oo.Order_code]
		}

		bb.Store_item_number = tt(bb.Store_item_number, oo.Store_item_number)
		bb.Product_id = tt(bb.Product_id, oo.Product_id)
		bb.Product_name = tt(bb.Product_name, oo.Product_name)
		bb.Product_quantity_str = tt(bb.Product_quantity_str, oo.Product_quantity_str)
		bb.Order_net_weight_str = tt(bb.Order_net_weight_str, oo.Order_net_weight_str)
		bb.Pack_weight_str = tt(bb.Pack_weight_str, oo.Pack_weight_str)
		bb.Purchase_cost_str = tt(bb.Purchase_cost_str, oo.Purchase_cost_str)
		bb.Store_item_id = tt(bb.Store_item_id, oo.Store_item_id)
		bb.Sku_name = tt(bb.Sku_name, oo.Sku_name)
		bb.Store_order_weight_str = tt(bb.Store_order_weight_str, oo.Store_order_weight_str)
		bb.Cn_title = tt(bb.Cn_title, oo.Cn_title)
		bb.Attr_trans = tt(bb.Attr_trans, oo.Attr_trans)
		bb.Sale_state_str = tt(bb.Sale_state_str, oo.Sale_state_str)
		bb.Bg_order_state = tt(bb.Bg_order_state, oo.Bg_order_state)
		bb.Pick_user = tt(bb.Pick_user, oo.Pick_user)
		bb.Pack_user = tt(bb.Pack_user, oo.Pack_user)
		bb.Developer_name = tt(bb.Developer_name, oo.Developer_name)
		bb.Designer_name = tt(bb.Designer_name, oo.Designer_name)
		bb.Op_staff = tt(bb.Op_staff, oo.Op_staff)
		bb.Purchaser_name = tt(bb.Purchaser_name, oo.Purchaser_name)
		bb.Weight_str = tt(bb.Weight_str, oo.Weight_str)
		oList[oo.Order_code] = bb
	}

	for _, order := range oList {
		orderType := reflect.TypeOf(order)
		orderValue := reflect.ValueOf(order)
		valCn := make([]string, 0)
		for _, fd := range realNeedFds {
			fkey := common.UFirst(fd.Field)
			if _, okey := orderType.FieldByName(fkey + "_str"); okey {
				fkey = fkey + "_str"
			}
			if _, ok := orderType.FieldByName(fkey); ok {
				val := orderValue.FieldByName(fkey)
				switch val.Kind() {
				case reflect.String:
					valCn = append(valCn, val.String())
				case reflect.Float64:
					valCn = append(valCn, strconv.FormatFloat(val.Float(), 'G', -1, 64))
				case reflect.Int:
					valCn = append(valCn, strconv.FormatInt(val.Int(), 10))
				case reflect.Int64:
					valCn = append(valCn, strconv.FormatInt(val.Int(), 10))
				case reflect.Bool:
					valCn = append(valCn, strconv.FormatBool(val.Bool()))
				}
			} else {
				valCn = append(valCn, "")
			}
		}
		items := make([]string, 0)
		for _, s := range valCn {
			s = strings.Replace(s, "_#", "*", -1)
			s = strings.Replace(s, "_|", ",", -1)
			items = append(items, s)
		}
		_ = w.Write(items)
		w.Flush()
	}

	wg.Done()
}

func tt(b, o string) string {
	if b == "" {
		if o != "" {
			b = o + "_#1"
		}
	} else {
		if o != "" {
			res := strings.Split(b, "_|")
			exist := false
			for k, re := range res {
				if strings.Contains(re, o) {
					exist = true
					if strings.Contains(re, o+"_#") {
						tt := strings.Split(re, "_#")
						c := common.IntVal(tt[1])
						res[k] = tt[0] + "_#" + strconv.Itoa(c+1)
					} else {
						res[k] = re + "_#1"
					}
				}
			}
			if !exist {
				res = append(res, o+"_#1")
			}
			b = strings.Join(res, "_|")
		}
	}
	return b
}

type FieldKv struct {
	Field     string `json:"key"`
	FieldName string `json:"field_name"`
}

func getNeetFields(form *f.IndexForm) []*FieldKv {
	fields, err := ioutil.ReadFile("export_fields.json")
	if err != nil {
		panic(err)
	}
	exportFields := make([]ExportField, 0)
	err = json.Unmarshal(fields, &exportFields)
	if err != nil {
		panic(err)
	}

	list := make([]*FieldKv, 0)
	for _, field := range exportFields {
		if field.E_name == "order_info" {
			for _, item := range field.List {
				for _, val := range form.OrderInfo {
					if val == item.Val {
						list = append(list, &FieldKv{Field: item.Key, FieldName: item.Val})
						break
					}
				}
			}
		}

		if field.E_name == "sku_info" {
			for _, item := range field.List {
				for _, val := range form.SkuInfo {
					if val == item.Val {
						list = append(list, &FieldKv{Field: item.Key, FieldName: item.Val})
						break
					}
				}
			}
		}

		if field.E_name == "person_info" {
			for _, item := range field.List {
				for _, val := range form.PersonInfo {
					if val == item.Val {
						list = append(list, &FieldKv{Field: item.Key, FieldName: item.Val})
						break
					}
				}
			}
		}

		if field.E_name == "trade_info" {
			for _, item := range field.List {
				for _, val := range form.TradeInfo {
					if val == item.Val {
						list = append(list, &FieldKv{Field: item.Key, FieldName: item.Val})
						break
					}
				}
			}
		}

		if field.E_name == "customer_info" {
			for _, item := range field.List {
				for _, val := range form.CustomerInfo {
					if val == item.Val {
						list = append(list, &FieldKv{Field: item.Key, FieldName: item.Val})
						break
					}
				}
			}
		}

		if field.E_name == "ship_info" {
			for _, item := range field.List {
				for _, val := range form.ShipInfo {
					if val == item.Val {
						list = append(list, &FieldKv{Field: item.Key, FieldName: item.Val})
						break
					}
				}
			}
		}

		if field.E_name == "store_info" {
			for _, item := range field.List {
				for _, val := range form.StoreInfo {
					if val == item.Val {
						list = append(list, &FieldKv{Field: item.Key, FieldName: item.Val})
						break
					}
				}
			}
		}

		if field.E_name == "order_remark" {
			for _, item := range field.List {
				for _, val := range form.OrderRemark {
					if val == item.Val {
						list = append(list, &FieldKv{Field: item.Key, FieldName: item.Val})
						break
					}
				}
			}
		}

		if field.E_name == "after_sale" {
			for _, item := range field.List {
				for _, val := range form.After_sale {
					if val == item.Val {
						list = append(list, &FieldKv{Field: item.Key, FieldName: item.Val})
						break
					}
				}
			}
		}
	}
	return list
}

func getOrderProfitDenominator(order ExcelOrder) float64 {
	outGrandTotal := 0.0
	switch order.Platform {
	case "amazon":
		area := new(model.AccountLogic).GetAmazonShopAreaByShopId(order.Shop_id)
		if area == "NA" {
			if order.Shipping_method_orig == "FBA" {
				outGrandTotal = amountToRMB(order.Subtotal+order.Order_discount, order.Currency_rate)
			} else {
				outGrandTotal = amountToRMB(order.Subtotal+order.Order_discount+order.Shipping_charge, order.Currency_rate)
			}
		} else if area == "EU" {
			if order.Shipping_method_orig == "FBA" {
				outGrandTotal = amountToRMB(order.Subtotal+order.Tax+order.Order_discount, order.Currency_rate)
			} else {
				outGrandTotal = amountToRMB(order.Subtotal+order.Order_discount+order.Shipping_charge, order.Currency_rate) + order.Shipping_cost
			}
		} else if area == "FE" {
			if order.Shipping_method_orig == "FBA" {
				outGrandTotal = amountToRMB(order.Subtotal+order.Tax+order.Order_discount, order.Currency_rate)
			} else {
				outGrandTotal = amountToRMB(order.Subtotal+order.Order_discount+order.Shipping_charge, order.Currency_rate) + order.Shipping_cost
			}
		}
	case "ebay":
	case "aliexpress":
	case "lazada":
	case "wordpress":
	case "opencart":
	case "shoplazza":
	case "walmart":
	case "fanno":
	case "shopee":
	case "alibaba":
	case "wish":
		outGrandTotal = amountToRMB(order.Grand_total+order.Tax, order.Currency_rate)
	case "shopify":
		outGrandTotal = amountToRMB(order.Grand_total+order.Tax+order.Shipping_free, order.Currency_rate)
	default:
		outGrandTotal = amountToRMB(order.Grand_total, order.Currency_rate)
	}
	return outGrandTotal
}

func amountToRMB(amount, currencyRate float64) float64 {
	if currencyRate > 0 {
		return amount * currencyRate
	} else {
		return amount
	}
}

type ExportField struct {
	C_name string `json:"c_name"`
	E_name string `json:"e_name"`
	List   []struct {
		Key string `json:"key"`
		Val string `json:"val"`
	}
}

type ExcelOrder struct {
	model.OrderMain               `xorm:"extends"`
	model.OrderProduct            `xorm:"extends"`
	SkuInfo                       `xorm:"extends"`
	OrderOther                    `xorm:"extends"`
	Sale_state_str                string  `json:"sale_state_str"`
	Order_status_str              string  `json:"order_status_str"`
	Order_net_weight              int     `json:"order_net_weight"`
	Purchase_cost                 float64 `json:"purchase_cost"`
	Shipping_company              string  `json:"shipping_company"`
	Mode_name                     string  `json:"mode_name"`
	After_sale_number             string  `json:"after_sale_number"`
	Reverse_type                  string  `json:"reverse_type"`
	Reverse_status                string  `json:"reverse_status"`
	Or_amount                     float64 `json:"or_amount"`
	Order_type_str                string  `json:"order_type_str"`
	Out_grand_total               float64 `json:"out_grand_total"`
	Order_profit_rate             float64 `json:"order_profit_rate"`
	Warehouse_name                string  `json:"warehouse_name"`
	Op_paypal_account             string  `json:"op_paypal_account"`
	Order_profit_rate_str         string  `json:"order_profit_rate_str"`
	Is_fba_behalf_str             string  `json:"is_fba_behalf_str"`
	Store_order_weight            float64 `json:"store_order_weight"`
	Bg_order_state                string  `json:"bg_order_state"`
	Op_staff                      string  `json:"op_staff"`
	Purchaser_name                string  `json:"purchaser_name"`
	Delay_days                    int64   `json:"delay_days"`
	Overseas_warehouse_status_str string  `json:"overseas_warehouse_status_str"`
	Product_quantity_str          string  `json:"product_quantity_str"`
	Order_net_weight_str          string  `json:"order_net_weight_str"`
	Pack_weight_str               string  `json:"pack_weight_str"`
	Weight_str                    string  `json:"weight_str"`
	Purchase_cost_str             string  `json:"purchase_cost_str"`
	Store_order_weight_str        string  `json:"store_order_weight_str"`
}

type SkuInfo struct {
	Sku            string  `json:"sku"`
	Pack_weight    int     `json:"pack_weight"`
	Weight         int     `json:"weight"`
	Designer       string  `json:"designer"`
	Sku_name       string  `json:"sku_name"`
	Buy_price      float64 `json:"buy_price"`
	Developer_name string  `json:"developer_name"`
	Designer_name  string  `json:"designer_name"`
	Attr_trans     string  `json:"attr_trans"`
	Sale_state     int     `json:"sale_state"`
	Purchaser      string  `json:"purchaser"`
}

type ShipMethod struct {
	Logistics_company_name string `json:"logistics_company_name"`
	Mode_name              string `json:"mode_name"`
	Mode_code              string `json:"mode_code"`
}

type OrderReverse struct {
	Or_code        string  `json:"or_code"`
	Rel_order_code string  `json:"rel_order_code"`
	Reverse_type   int     `json:"reverse_type"`
	Reverse_status int     `json:"reverse_status"`
	Or_amount      float64 `json:"or_amount"`
}

type OrderOther struct {
	Pick_user         string `json:"pick_user"`
	Pack_user         string `json:"pack_user"`
	Ex_warehouse_time string `json:"ex_warehouse_time"`
}

type OrderPayment struct {
	Op_paypal_account  string `json:"op_paypal_account"`
	Transaction_number string `json:"transaction_number"`
}
